package top.huhuiyu.javabasic.javabasictemplate.dao;

import top.huhuiyu.javabasic.javabasictemplate.entity.Page;
import top.huhuiyu.javabasic.javabasictemplate.entity.TbNews;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class TbNewsDAO {
    public static final String INSERT = "insert into tb_news(title,info,source) values(?,?,?)";
    public static final String QUERY = "select nid,title,hits,source,lastupdate from tb_news";
    public static final String QUERY_COUNT = "select count(*) as 'total' from tb_news";
    private static String sql;

    public static boolean isBlank(String info) {
        return info == null || info.trim().isEmpty();
    }


    public static List<TbNews> query(TbNews news, Page page) throws SQLException {
        String sql = QUERY;
        String countSql = QUERY_COUNT;
        List<Object> params = new ArrayList<>();
        // 动态sql
        if (!isBlank(news.getTitle())) {
            sql += " and title like ?";
            countSql += " and title like ?";
            params.add("%" + news.getTitle() + "%");
        }
        if (!isBlank(news.getSource())) {
            sql += " and source like ?";
            countSql += " and source like ?";
            params.add("%" + news.getSource() + "%");
        }
        if (!params.isEmpty()) {
            sql = sql.replaceFirst("and", "where");
            countSql = countSql.replaceFirst("and", "where");
        }
        Connection connection = DBUtils.getConnection();
        // 记录数查询
        PreparedStatement cstatement = connection.prepareStatement(countSql);
        // sql参数处理
        for (int i = 0; i < params.size(); i++) {
            cstatement.setObject(i + 1, params.get(i));
        }
        System.out.println("记录数查询：" + countSql);
        System.out.println("记录数参数：" + params);
        ResultSet rs = cstatement.executeQuery();
        rs.next();
        int count = rs.getInt("total");
        page.setTotal(count);
        rs.close();
        cstatement.close();
        System.out.println("记录数查询结果：" + count);
        // 排序
        sql += " order by nid desc";
        // 分页处理
        sql += " limit ?,?";
        System.out.printf("sql:%s%n,参数：%s%n", sql, params);
        PreparedStatement statement = connection.prepareStatement(sql);
        // sql参数处理
        for (int i = 0; i < params.size(); i++) {
            statement.setObject(i + 1, params.get(i));
        }
        // 分月参数
        statement.setInt(params.size() + 1, page.getStart());
        statement.setInt(params.size() + 2, page.getPageSize());
        ResultSet resultSet = statement.executeQuery();
        List<TbNews> list = new ArrayList<>();
        while (resultSet.next()) {
            TbNews tbNews = new TbNews();
            tbNews.setNid(resultSet.getInt("nid"));
            tbNews.setTitle(resultSet.getString("title"));
            tbNews.setHits(resultSet.getInt("hits"));
            tbNews.setSource(resultSet.getString("source"));
            tbNews.setLastupdate(resultSet.getTimestamp("lastupdate"));
            list.add(tbNews);
        }
        resultSet.close();
        statement.close();
        connection.close();
        return list;
    }

    public static int add(TbNews tbNews) throws SQLException {
        Connection connection = DBUtils.getConnection();
        PreparedStatement statement = connection.prepareStatement(INSERT, PreparedStatement.RETURN_GENERATED_KEYS);
        statement.setString(1, tbNews.getTitle());
        statement.setString(2, tbNews.getInfo());
        statement.setString(3, tbNews.getSource());
        int count = statement.executeUpdate();
        ResultSet rs = statement.getGeneratedKeys();
        if (rs.next()) {
            tbNews.setNid(rs.getInt(1));
        }
        statement.close();
        connection.close();
        return count;
    }

}